package com.affablebeans.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.affablebeans.model.entity.Category;
import com.affablebeans.model.entity.Product;

/**
 *
 * @author Bora SAYINER
 * @since Jan 26, 2015 4:23:17 PM
 * @version 1.0
 */
public class ProductDAL extends AbstractDAL<Product> {

	private static final String INSERT_SQL = "INSERT INTO product (name,price,description,last_update,category_id) VALUES (?,?,?,?,?)";
	private static final String UPDATE_SQL = "UPDATE product SET name=?,price=?,description=?,last_update=?,category_id=? WHERE id=?";
	private static final String DELETE_SQL = "DELETE FROM product WHERE id=?";
	private static final String FIND_BY_ID_SQL = "SELECT p.id,p.name,p.price,p.description,p.last_update,c.id as category_id,c.name as category_name FROM product AS p INNER JOIN category AS c ON c.id=p.category_id WHERE p.id=?";
	private static final String GET_ALL_SQL = "SELECT p.id,p.name,p.price,p.description,p.last_update,c.id as category_id,c.name as category_name FROM product AS p INNER JOIN category AS c ON c.id=p.category_id;";
	private static final String FIND_BY_CATEGORY_SQL = "SELECT p.id,p.name,p.price,p.description,p.last_update,c.id as category_id,c.name as category_name FROM product AS p INNER JOIN category AS c ON c.id=p.category_id WHERE c.id=?";

	public ProductDAL(DataSource dataSource) {
		super(dataSource, "product");
	}

	@Override
	public boolean insert(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
				statement.setString(1, entity.getName());
				statement.setDouble(2, entity.getPrice());
				statement.setString(3, entity.getDescription());
				statement.setDate(4, convertToDate(entity.getLastUpdate()));
				statement.setInt(5, entity.getCategory().getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					ResultSet generatedKeys = statement.getGeneratedKeys();
					if (generatedKeys.next()) {
						entity.setId(generatedKeys.getInt(1));
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean update(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(UPDATE_SQL)) {
				statement.setString(1, entity.getName());
				statement.setDouble(2, entity.getPrice());
				statement.setString(3, entity.getDescription());
				statement.setDate(4, convertToDate(entity.getLastUpdate()));
				statement.setInt(5, entity.getCategory().getId());
				statement.setInt(6, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean delete(Product entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(DELETE_SQL)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public Product findById(int... id) throws SQLException {
		Product product = null;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(FIND_BY_ID_SQL)) {
				statement.setInt(1, id[0]);
				ResultSet resultSet = statement.executeQuery();
				if (resultSet.next()) {
					product = createEntity(resultSet);
				}
			}
		}
		return product;
	}

	@Override
	public List<Product> getAll() throws SQLException {
		List<Product> products = new ArrayList<Product>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				ResultSet resultSet = statement.executeQuery(GET_ALL_SQL);
				while (resultSet.next()) {
					products.add(createEntity(resultSet));
				}
			}
		}
		return products;
	}

	public List<Product> findByCategory(int id) throws SQLException {
		List<Product> products = new ArrayList<Product>();
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(FIND_BY_CATEGORY_SQL)) {
				statement.setInt(1, id);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					products.add(createEntity(resultSet));
				}
			}
		}
		return products;
	}

	@Override
	Product createEntity(ResultSet resultSet) throws SQLException {
		Product product = new Product();
		product.setId(resultSet.getInt("id"));
		product.setName(resultSet.getString("name"));
		product.setPrice(resultSet.getDouble("price"));
		product.setDescription(resultSet.getString("description"));
		product.setLastUpdate(resultSet.getDate("last_update"));
		{
			Category category = new Category();
			category.setId(resultSet.getInt("category_id"));
			category.setName(resultSet.getString("category_name"));
			product.setCategory(category);
		}
		return product;
	}

}
